# 配置模块 - 加载环境变量
import os
import pandas as pd
from sqlalchemy import create_engine

# 加载环境变量，从 env.xlsx 文件中读取
def load_env_db():
    env_db = pd.read_excel('env.xlsx', sheet_name='db').set_index('KEY')['VALUE'].to_dict()
    DB_USER = env_db['DB_USER']
    DB_PASSWORD = env_db['DB_PASSWORD']
    DB_HOST = env_db['DB_HOST']
    DB_PORT = env_db['DB_PORT']
    DB_SERVICE_NAME = env_db['DB_SERVICE_NAME']
    DB_DATABASE = env_db['DB_DATABASE']

    # 打印数据库连接信息（调试用）
    # print("DB_USER:", DB_USER)
    # print("DB_PASSWORD:", DB_PASSWORD)
    # print("DB_HOST:", DB_HOST)
    # print("DB_PORT:", DB_PORT)
    # print("DB_SERVICE_NAME:", DB_SERVICE_NAME)
    # print("DB_DATABASE:", DB_DATABASE)
    return DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_SERVICE_NAME, DB_DATABASE

def load_env_tbl_name():
    # 获取各表名称信息
    env_tbl_name = pd.read_excel('env.xlsx', sheet_name='tbl_name').set_index('KEY')['VALUE'].to_dict()
    SETL_TBL_NAME = env_tbl_name['SETL_TBL_NAME']
    MDTRT_TBL_NAME = env_tbl_name['MDTRT_TBL_NAME']
    FEE_TBL_NAME = env_tbl_name['FEE_TBL_NAME']
    DX_TBL_NAME = env_tbl_name['DX_TBL_NAME']
    TX_TBL_NAME = env_tbl_name['TX_TBL_NAME']
    # print("Table Names Loaded.")
    return SETL_TBL_NAME, MDTRT_TBL_NAME, FEE_TBL_NAME, DX_TBL_NAME, TX_TBL_NAME

def load_env_structure_setl():
    # 获取各列名信息
    env_structure = pd.read_excel('env.xlsx', sheet_name='env_structure').set_index(['tbl_name', 'core'])['field_code'].to_dict()
    # setl表
    SETL_SETL_ID = env_structure[('setl', 'setl_id')]
    SETL_MDTRT_ID = env_structure[('setl', 'mdtrt_id')]
    SETL_PSN_NO = env_structure[('setl', 'psn_no')]
    SETL_HSP_ID = env_structure[('setl', 'hsp_code')]
    SETL_SETL_TIME = env_structure[('setl', 'setl_time')]
    SETL_GNR_C = env_structure[('setl', 'gnr_c')]
    SETL_GNR_B = env_structure[('setl', 'gnr_b')]
    # SETL_ISDROP = env_structure[('setl', 'is_drop')]
    SETL_IN_DATE_CHECK = env_structure[('setl', 'in_date0')]
    SETL_OUT_DATE_CHECK = env_structure[('setl', 'out_date0')]
    SETL_VALIDFLAG = env_structure[('setl', 'vali_flag')]
    # SETL_IN_DX_NAME_CHECK = env_structure[('setl', 'in_dx_name0')]
    return (SETL_SETL_ID, SETL_MDTRT_ID, SETL_PSN_NO, SETL_HSP_ID, SETL_SETL_TIME,
            SETL_GNR_C, SETL_GNR_B, SETL_IN_DATE_CHECK, SETL_OUT_DATE_CHECK, SETL_VALIDFLAG)

def load_env_structure_mdtrt():
    env_structure = pd.read_excel('env.xlsx', sheet_name='env_structure').set_index(['tbl_name', 'core'])['field_code'].to_dict()
    # mdtrt表
    MDTRT_MDTRT_ID = env_structure[('mdtrt', 'mdtrt_id')]
    # MDTRT_SETL_ID = env_structure[('mdtrt', 'setl_id')]
    MDTRT_IN_OUT_ID = env_structure[('mdtrt', 'in_out_id')]
    MDTRT_HSP_ID = env_structure[('mdtrt', 'hsp_code')]
    MDTRT_HSP_NAME = env_structure[('mdtrt', 'hsp_name')]
    MDTRT_HSP_LV = env_structure[('mdtrt', 'hsp_lv_raw')]
    MDTRT_PSN_NO = env_structure[('mdtrt', 'psn_no')]
    MDTRT_CERTNO = env_structure[('mdtrt', 'certno')]
    MDTRT_CERT_TYPE = env_structure[('mdtrt', 'psn_cert_type')]
    MDTRT_PSN_NAME = env_structure[('mdtrt', 'psn_name')]
    MDTRT_GEND0 = env_structure[('mdtrt', 'gend')]
    MDTRT_BRDY = env_structure[('mdtrt', 'brdy')]
    MDTRT_AGE = env_structure[('mdtrt', 'age')]
    MDTRT_INSUTYPE0 = env_structure[('mdtrt', 'insutype')]
    MDTRT_IN_TIME = env_structure[('mdtrt', 'in_time')]
    MDTRT_OUT_TIME = env_structure[('mdtrt', 'out_time')]
    MDTRT_IN_DEPT = env_structure[('mdtrt', 'in_dept')]
    MDTRT_OUT_DEPT = env_structure[('mdtrt', 'out_dept')]
    # MDTRT_J_ISIN0 = env_structure[('mdtrt', 'j_isin')]
    MDTRT_VALIDFLAG = env_structure[('mdtrt', 'vali_flag')]
    MDTRT_OUT_DX_CODE_CHECK = env_structure[('mdtrt', 'out_dx_code')]
    MDTRT_OUT_DX_NAME_CHECK = env_structure[('mdtrt', 'out_dx_name')]
    MDTRT_OUTP_DX_CHECK = env_structure[('mdtrt', 'otp_diag_info')]
    return (MDTRT_MDTRT_ID, MDTRT_IN_OUT_ID, MDTRT_HSP_ID, MDTRT_HSP_NAME,
            MDTRT_HSP_LV, MDTRT_PSN_NO, MDTRT_CERTNO, MDTRT_CERT_TYPE,
            MDTRT_PSN_NAME, MDTRT_GEND0, MDTRT_BRDY, MDTRT_AGE,
            MDTRT_INSUTYPE0, MDTRT_IN_TIME, MDTRT_OUT_TIME,
            MDTRT_IN_DEPT, MDTRT_OUT_DEPT, MDTRT_VALIDFLAG,
            MDTRT_OUT_DX_CODE_CHECK, MDTRT_OUT_DX_NAME_CHECK,
            MDTRT_OUTP_DX_CHECK)

def load_env_structure_fee():
    env_structure = pd.read_excel('env.xlsx', sheet_name='env_structure').set_index(['tbl_name', 'core'])['field_code'].to_dict()
    # fee表
    FEE_SETL_ID = env_structure[('fee', 'setl_id')]
    FEE_MDTRT_ID = env_structure.get(('fee', 'mdtrt_id'), None)
    FEE_HSP_ID = env_structure[('fee', 'hsp_code')]
    FEE_PSN_NO = env_structure[('fee', 'psn_no')]
    FEE_MED_TYPE0 = env_structure[('fee', 'med_type')]
    FEE_ITEM_TYPE = env_structure[('fee', 'item_type_raw')]
    FEE_ITEM_TIME = env_structure[('fee', 'item_time')]
    FEE_ITEM_CODE = env_structure[('fee', 'item_code')]
    FEE_ITEM_NAME = env_structure[('fee', 'item_name')]
    FEE_ITEM_MED_CODE = env_structure[('fee', 'item_med_code')]
    FEE_ITEM_HSP_CODE = env_structure[('fee', 'item_hsp_code')]
    FEE_ITEM_HSP_NAME = env_structure[('fee', 'item_hsp_name')]
    FEE_ITEM_TYPE0 = env_structure[('fee', 'item_type_raw')]
    FEE_Q = env_structure[('fee', 'q_raw')]
    FEE_P = env_structure[('fee', 'p')]
    FEE_C = env_structure[('fee', 'c_raw')]
    FEE_B = env_structure[('fee', 'b_raw')]
    FEE_VALIDFLAG = env_structure[('fee', 'vali_flag')]
    return (FEE_SETL_ID, FEE_MDTRT_ID, FEE_HSP_ID, FEE_PSN_NO, FEE_MED_TYPE0,
            FEE_ITEM_TYPE, FEE_ITEM_TIME, FEE_ITEM_CODE, FEE_ITEM_NAME,
            FEE_ITEM_MED_CODE, FEE_ITEM_HSP_CODE, FEE_ITEM_HSP_NAME,
            FEE_ITEM_TYPE0, FEE_Q, FEE_P, FEE_C, FEE_B, FEE_VALIDFLAG)

def load_env_structure_dx():
    env_structure = pd.read_excel('env.xlsx', sheet_name='env_structure').set_index(['tbl_name', 'core'])['field_code'].to_dict()
    # dx表
    DX_SETL_ID = env_structure[('dx', 'setl_id')]
    DX_DIAG_TYPE = env_structure[('dx', 'dx_type')]
    DX_IS_MAIN = env_structure[('dx', 'is_main')]
    DX_DX_CODE = env_structure[('dx', 'dx_code')]
    DX_DX_NAME = env_structure[('dx', 'dx_hsp_name')]
    DX_VALIDFLAG = env_structure[('dx', 'vali_flag')]
    return (DX_SETL_ID, DX_DIAG_TYPE, DX_IS_MAIN, DX_DX_CODE, DX_DX_NAME, DX_VALIDFLAG)

def load_env_structure_tx():
    env_structure = pd.read_excel('env.xlsx', sheet_name='env_structure').set_index(['tbl_name', 'core'])['field_code'].to_dict()
    # tx表
    TX_SETL_ID = env_structure[('tx', 'setl_id')]
    TX_IS_MAIN = env_structure[('tx', 'is_main')]
    TX_TX_CODE = env_structure[('tx', 'tx_code')]
    TX_TX_NAME = env_structure[('tx', 'tx_hsp_name')]
    TX_TX_TIME = env_structure[('tx', 'tx_date')]
    TX_VALIDFLAG = env_structure[('tx', 'vali_flag')]
    return (TX_SETL_ID, TX_IS_MAIN, TX_TX_CODE, TX_TX_NAME, TX_TX_TIME, TX_VALIDFLAG)

# 获取医院别名列表
def load_env_hsp_abbr():
    env_hsp_abbr = pd.read_excel('env.xlsx', sheet_name='hsp_abbr').to_dict(orient='records')
    hspListRaw = []
    for i, record in enumerate(env_hsp_abbr):
        globals()[f'HSPCODE{i}'] = record['HSP_CODE']
        globals()[f'HSPABBR{i}'] = record['HSP_ABBR']
        hspListRaw.append((record['HSP_CODE'], record['HSP_ABBR']))
    # print("Hospital Abbreviations Loaded.")
    return hspListRaw

# 创建数据库引擎
def create_db_engine():
    DB_USER, DB_PASSWORD, DB_HOST, DB_PORT, DB_SERVICE_NAME, DB_DATABASE = load_env_db()
    return create_engine(f"oracle+oracledb://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/?service_name={DB_SERVICE_NAME}")
